package dao;

import model.BookModel;
import util.WebSiteUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BookDAO {
    /**
     * 创建书籍对象
     * @param model
     * @return
     */
    public boolean createBook(BookModel model){
        boolean rs = false;
        String sql = "insert into book(bookname,author,publisher,pic,categoryid) values (?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,model.getBookName());
            ps.setString(2,model.getAuthor());
            ps.setString(3,model.getPublisher());
            ps.setString(4,model.getPic());
            ps.setInt(5,model.getCategoryId());
            ps.execute();
            rs = true;
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return rs;
    }
    /**
     * 根据书籍ID删除书籍信息
     * @param id
     * @return
     */
    public boolean deleteBookById(int id){
        boolean rs = false;
        String sql = "delete from book where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            if(ps.executeUpdate()==1){
                rs = true;
            }else{
                rs = false;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return rs;
    }

    /**
     * 更新书籍信息
     * @param model
     * @return
     */
    public boolean updateBook(BookModel model){
        boolean rs = false;
        String sql = "update book set bookname=?,author=?,publisher=?,pic=?,categoryid=? where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,model.getBookName());
            ps.setString(2,model.getAuthor());
            ps.setString(3,model.getPublisher());
            ps.setString(4,model.getPic());
            ps.setInt(5,model.getCategoryId());
            ps.setInt(6,model.getId());
            int s = ps.executeUpdate();
            if(s>0)
                rs = true;
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return rs;
    }
    /**
     * 根据ID查询书籍信息
     * @param id
     * @return
     */
    public BookModel getBookById(int id){
        String sql = "select * from book where id =?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                BookModel b = new BookModel();
                b.setId(rs.getInt("id"));
                b.setCategoryId(rs.getInt("categoryid"));
                b.setAuthor(rs.getString("author"));
                b.setBookName(rs.getString("bookname"));
                b.setPublisher(rs.getString("publisher"));
                b.setPic(rs.getString("pic"));
                return b;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }

        return null;
    }
    /**
     * 根据相关条件查询该类目下的所有图书
     * @param categoryId
     * @return
     */
    public List<BookModel> findBookByQuery(int categoryId,String bookName){
        List<BookModel> bookList = new ArrayList<BookModel>();
        String sql = "select b.*,ca.categoryname from book b,category ca where b.categoryid=ca.categoryid ";
        if(categoryId>0)
            sql +=" and b.categoryid=? " ;
        if(WebSiteUtil.isNotEmpty(bookName))
            sql +=" and b.bookname like '%"+bookName+"%'";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            //categoryId 大于0时作为有效的查询条件，否则将不作为查询条件
            if(categoryId>0)
                ps.setInt(1,categoryId);
            rs = ps.executeQuery();
            while(rs.next()){
                BookModel b = new BookModel();
                b.setId(rs.getInt("id"));
                b.setCategoryId(rs.getInt("categoryid"));
                b.setAuthor(rs.getString("author"));
                b.setBookName(rs.getString("bookname"));
                b.setPublisher(rs.getString("publisher"));
                b.setPic(rs.getString("pic"));
                b.setCategoryName(rs.getString("categoryname"));
                bookList.add(b);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return bookList;
    }


}
